<?

class M_employee Extends MY_Model
{
	private $start;
	private $end;
	private $month;
	private $year;
	
	public function __construct()
	{
		parent::__construct();
		$this->_date_time();
	}
	
	public function get($args,$id)
	{
		$query = $this->db->select($args)
						  ->where('id',$id)
						  ->get( parent::$emp_table);
		return $query->num_rows() >= 1 ? $query->row() : FALSE;
	}
	
	
	// inserts new employee to database
	public function new_employee($data)
	{	
		unset($data['add_employee']);
		$data['created_at'] = NOW;
		$this->db->insert(parent::$emp_table,$data);
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	private function _date_time()
	{
		$this->month = $m = date('m');
		$this->year = $y = date('Y');
		if(date('j') < 15)
		{
			$this->start = sprintf('%d-%s-1',$y,$m);
			$this->end = sprintf('%d-%s-15',$y,$m);
		}else{
			$this->start = sprintf('%d-%s-16',$y,$m);
			$this->end = sprintf('%d-%s-30',$y,$m);
		}
	}
	
	//retrives all employees
	public function all_employee()
	{
		$e_table = parent::$emp_table;
		$p_table = parent::$pos_table;
		$d_table = parent::$dep_table;
		$sql = "SELECT e.id,
					   e.firstname,
					   e.lastname,
					   e.middlename,
					   e.gender,
					   e.address,
					   e.status,
					   e.dependents,
					   p.position_name as position,
					   e.monthly_salary,
					   e.created_at,
					   d.department_name as dept_name
				FROM {$e_table} e
				LEFT JOIN {$d_table} d ON d.id = e.department
				LEFT JOIN {$p_table} p ON p.id = e.position
				ORDER BY e.lastname ASC
				";
		$query = $this->db->query($sql);
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	//retreive a specific employee
	public function one_employee($id)
	{
		$e_table = parent::$emp_table;
		$p_table = parent::$pos_table;
		$d_table = parent::$dep_table;
		$sql = "SELECT e.id,
					   e.firstname,
					   e.lastname,
					   e.middlename,
					   e.gender,
					   e.address,
					   e.status,
					   e.dependents,
					   e.department as pid,
					   p.position_name as pname,
					   e.monthly_salary,
					   e.created_at,
					   d.id as dept_name
				FROM {$e_table} e
				LEFT JOIN {$d_table} d ON d.id = e.department
				LEFT JOIN {$p_table} p ON p.id = e.position
				WHERE e.id = ?
				";
		$query = $this->db->query($sql,array($id));
		return $query->num_rows() >=1 ? $query->row() : FALSE;	
	}
	
	// updates employee records
	public function update_employee($data,$id)
	{
		unset($data['update_employee']);
		$data['updated_at'] = NOW;
		$this->db->set($data)->where('id',$id)->update(parent::$emp_table);
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function add_deductions($data,$id)
	{
		$this->db->update(parent::$emp_table,$data,array('id' => $id));
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function add_withholding_tax($data,$id)
	{
		$this->db->update(parent::$emp_table,$data,array('id' => $id));
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	// retreive a;; employees and attendance
	public function get_employee_for_attendance()
	{
		$e_table = parent::$emp_table;
		$a_table = parent::$att_table;
		
		$sql = "SELECT concat(e.firstname,' ',e.lastname,' ',e.middlename) as fullname,
				a.am_in,a.am_out,a.pm_in,a.pm_out,a.id,a.leave,a.absent,a.ot_holiday,a.ot_restday
				FROM {$e_table} e 
				LEFT JOIN {$a_table} a ON a.emp_id = e.id
				WHERE a.date = ? ";
		
		$q = $this->db->query($sql,array(DATE));
		
		if($q->num_rows() >=1)
		{
			return array('data'=>$q->result(),'status'=>'has_attendance');
		}else{
			$sql = "SELECT id,
						   concat(firstname,' ',lastname,' ',middlename) as fullname
					FROM {$e_table}";
			$query = $this->db->query($sql);
			return $query->num_rows() >=1 ? array('data'=>$query->result(),'status'=>'no_attendance') : FALSE;
		}
	}
	
	// retrieves new added employees if not added on the attendance DTR
	public function get_nemployee_for_attendance()
	{
		$e_table = parent::$emp_table;
		$a_table = parent::$att_table;
		$p_table = parent::$pos_table;
		
		$sql = "SELECT concat(e.firstname,' ',e.lastname,' ',e.middlename) as fullname,e.id as emp_id,p.position_name as position
				FROM {$e_table} e
				LEFT JOIN {$p_table} p ON e.position = p.id
				WHERE e.id NOT IN (SELECT emp_id FROM {$a_table} WHERE date = ? )";
		$q = $this->db->query($sql,array(DATE,DATE));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	// inserts a attendance or DTR
	public function insert_attendance($data)
	{	
		$this->db->insert_batch(parent::$att_table,$data);
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	// updated the DTR or attendance
	public function update_attendance($data)
	{
		function data_is_set($key,$array)
		{
			if(array_key_exists($key,$array))
			{
				return 1;
			}else{
				return 0;
			}
		}
		
		foreach($data as $k => $v)
		{
			$filtered_input[$k]['am_in'] = $v['am_in']; 
			$filtered_input[$k]['am_out'] = $v['am_out']; 
			$filtered_input[$k]['pm_in'] = $v['pm_in']; 
			$filtered_input[$k]['pm_out'] = $v['pm_out']; 
			$filtered_input[$k]['id'] = $v['emp_id'];
			$filtered_input[$k]['absent'] = data_is_set('absent',$v);
			$filtered_input[$k]['leave'] = data_is_set('leave',$v);
			$filtered_input[$k]['ot_holiday'] = data_is_set('ot_holiday',$v);
			$filtered_input[$k]['ot_restday'] = data_is_set('ot_restday',$v);
		}
	
		$this->db->update_batch(parent::$att_table,$filtered_input,'id');
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	//check user login
	public function check_login($data,$id)
	{
		$check_login_sql = $this->db->select('count(id) as numrows')
									->from(parent::$usr_table)
									->where('hr_employee_id',$id)
									->limit(1)
									->get();
		$result = $check_login_sql->row()->numrows;
		
		
		$ci =& get_instance();
		$ci->load->model('M_auth','a');
		$password = $ci->a->_create_password($data['password']);
		
		$input['employee_username'] = $data['username']; 
		$input['employee_password'] = $password;
		$input['hr_employee_id'] = $id;
		$input['login_status'] = 1;
		$input['login_role'] = $data['role'];
		
		if($result >= 1)
		{
			unset($input['hr_employee_id']);
			$input['updated_at'] = NOW;
			$this->db->where('hr_employee_id',$id)->update(parent::$usr_table,$input);
			return $this->db->affected_rows() >= 1 ? TRUE: FALSE;
		}else{
			$input['created_at'] = NOW;
			$this->db->insert(parent::$usr_table,$input);
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}
	}
	
	// retrieves all employee and returns array, use for dropdown
	public function get_employee_array()
	{
		$q = $this->db->select('firstname,lastname,middlename,id')
				  ->get(parent::$emp_table);
				 
		if($q->num_rows() >=1 )
		{
			$opt[''] = ' -- Choose Employee -- ';
			foreach($q->result() as $v)
			{
				$opt[$v->id] = strtoupper($v->lastname).' , '.ucwords($v->firstname).' '.strtoupper($v->middlename[0]).'.'; 
			}
			return $opt;
		}else{
			return FALSE;
		}
	}
	
	// retrieve employees attendance either 1-15 or 16-30/31 
	public function get_employee_attendance($id)
	{
		$q = $this->db->select('e.id,a.am_in,a.am_out,a.pm_in,a.pm_out,a.date,p.position_name as position,e.monthly_salary,a.absent,a.leave')
					  ->from(parent::$att_table.' a')
					  ->join(parent::$emp_table.' e','e.id = a.emp_id','LEFT')
					  ->join(parent::$pos_table.' p','p.id = e.position','LEFT')
					  ->where('a.emp_id',$id)
					  ->where('date >=',$this->start)
					  ->where('date <=',$this->end)
					  ->get();
		if($q->num_rows() >= 1)
		{	
			foreach($q->result() as $k => $v)
			{
				if($v->absent == 1)
				{
					$data['employee_data'][$k]['am_out'] = 'Absent';
					$data['employee_data'][$k]['am_in'] = 'Absent';
					$data['employee_data'][$k]['pm_in'] = 'Absent';
					$data['employee_data'][$k]['pm_out'] = 'Absent';
				}elseif($v->leave == 1){
					$data['employee_data'][$k]['am_out'] = 'On Leave';
					$data['employee_data'][$k]['am_in'] = 'On Leave';
					$data['employee_data'][$k]['pm_in'] = 'On Leave';
					$data['employee_data'][$k]['pm_out'] = 'On Leave';
					$data['employee_data'][$k]['pm_out'] = 'On Leave';
				}else{
					$data['employee_data'][$k]['am_out'] = $v->am_out;
					$data['employee_data'][$k]['am_in'] = $v->am_in;
					$data['employee_data'][$k]['pm_in'] = $v->pm_in;
					$data['employee_data'][$k]['pm_out'] = $v->pm_out;
				}
					$data['employee_data'][$k]['id'] = $v->id;
					$data['employee_data'][$k]['position'] = $v->position;
					$data['employee_data'][$k]['monthly_salary'] = $v->monthly_salary;
					$data['employee_data'][$k]['date'] = $v->date;
			}
			
			$data['last_query'] = $this->db->last_query();
			$data['status'] = 'true';
			return $data;
		}
		else
		{
			return array('status'=>'false');
		}
	}
	
	// fetches employees absences for the month with dates 1-15 or 16-30/31
	public function get_employee_absences($id)
	{
		$sql = 'SELECT count(absent) as absences FROM '.parent::$att_table.' a 
				WHERE a.emp_id = ? 
				AND (a.date >= ? AND a.date <= ? ) 
				AND YEAR(a.date) = ?
				AND MONTH(a.date) = ? 
				AND absent = 1
				AND a.leave = 0
				AND a.ot_holiday = 0
				AND a.ot_restday = 0
				';
		$q = $this->db->query($sql,array($id,$this->start,$this->end,$this->year,$this->month));
		return $q->num_rows() >=1 ? $q->row()->absences : FALSE;
	}
	
	// public function get all undertime and return in minutes
	public function get_all_undertime($id,$time)
	{
		$time = $time.':00';
		$sql = 'SELECT pm_out FROM '.parent::$att_table.' a 
				WHERE a.emp_id = ? 
				AND (a.date >= ? AND a.date <= ? ) 
				AND YEAR(a.date) = ?
				AND MONTH(a.date) = ?
				AND a.pm_out <= ?
				AND a.absent = 0
				AND a.leave = 0
				AND a.ot_holiday = 0
				AND a.ot_restday = 0
				' ;
		$query = $this->db->query($sql,array($id,$this->start,$this->end,$this->year,$this->month,$time));
		
		if($query->num_rows() >= 1)
		{
			return $this->_time_to_minutes($query->result(),$time);
		}else{
			return 1;
		}
	}
	
	public function get_all_regular_overtime($id,$hourly_rate,$time,$overtime)
	{
		$time_hour = substr($time,0,strpos($time,':'));
		$time_min = substr($time,strpos($time,':')+1);
		
		$minutes = ($time_hour * 60) + $time_min + $overtime;
		$t=$this->_min2hr($minutes);
		$time_overtime = $t->hours.':'.$t->minutes;
		
		$sql = 'SELECT pm_out FROM '.parent::$att_table.' a
				WHERE a.emp_id = ? 
				AND (a.date >= ? AND a.date <= ? ) 
				AND YEAR(a.date) = ?
				AND MONTH(a.date) = ?
				AND a.pm_out > ?
				AND a.absent = 0
				AND a.leave = 0
				AND a.ot_holiday = 0
				AND a.ot_restday = 0';
				
		$query = $this->db->query($sql,array($id,$this->start,$this->end,$this->year,$this->month,$time_overtime));
		
		if($query->num_rows() >= 1)
		{
			$reg_ot_time = $query->result();
			
			
			foreach($reg_ot_time as $k => $v)
			{
				$time_total[$k] =  $this->_time_to_minutes($time_overtime,$v->pm_out);
			}
	
			$reg_overtime= $this->_min2hr(array_sum($time_total));
			return $reg_overtime->hours.'.'.$reg_overtime->minutes;
		}else{
			return 0;
		}
	}
	
	public function get_all_holiday_overtime($id,$hourly_rate,$time,$overtime)
	{
		$sql = 'SELECT am_in,am_out,pm_in,pm_out FROM '.parent::$att_table.' a
				WHERE a.emp_id = ? 
				AND (a.date >= ? AND a.date <= ? ) 
				AND YEAR(a.date) = ?
				AND MONTH(a.date) = ?
				AND a.absent = 0
				AND a.leave = 0
				AND a.ot_holiday = 1
				AND a.ot_restday = 0';
				
		$query = $this->db->query($sql,array($id,$this->start,$this->end,$this->year,$this->month));
		
		if($query->num_rows() >= 1)
		{
			$overtime_time = $query->result();
			
			
			foreach($overtime_time as $k => $v)
			{
				$time_total[$k] =  round($this->_time_to_minutes($v->am_in,$v->am_out),2) + round($this->_time_to_minutes($v->pm_in,$v->pm_out),2);
			}
			
			$holiday_overtime= $this->_min2hr(array_sum($time_total));
			return $holiday_overtime->hours.'.'.$holiday_overtime->minutes;
		}else
		{
			return 0;
		}
	}
	
	public function get_all_restday_overtime($id,$hourly_rate)
	{	
		$sql = 'SELECT am_in,am_out,pm_in,pm_out FROM '.parent::$att_table.' a
				WHERE a.emp_id = ? 
				AND (a.date >= ? AND a.date <= ? ) 
				AND YEAR(a.date) = ?
				AND MONTH(a.date) = ?
				AND a.absent = 0
				AND a.leave = 0
				AND a.ot_holiday = 0
				AND a.ot_restday = 1';
				
		$query = $this->db->query($sql,array($id,$this->start,$this->end,$this->year,$this->month));
		
		if($query->num_rows() >= 1)
		{
			$overtime_time = $query->result();
			
			
			foreach($overtime_time as $k => $v)
			{
				$time_total[$k] =  round($this->_time_to_minutes($v->am_in,$v->am_out),2) + round($this->_time_to_minutes($v->pm_in,$v->pm_out),2);
			}

			$restday_overtime= $this->_min2hr(array_sum($time_total));
			return $restday_overtime->hours.'.'.$restday_overtime->minutes;
		}else
		{
			return 0;
		}
	}
	
	public function compute_withholding_tax($total_gross_salary,$id)
	{
		$data = $this->get('status,dependents',$id);
		
		if($data !== FALSE)
		{
			$status = $data->status;
			switch($dep = $data->dependents)
			{
				case $dep == 0:
					$col = 's_me';
				break;
				case $dep == 1:
					$col = 'me1_s1';
				break;
				case $dep == 2:
					$col = 'me2_s2';
				break;
				case $dep == 3:
					$col = 'me3_s3';
				break;
				case $dep == 4:
				case $dep >= 4:
					$col = 'me4_s4';
				break;
			}
			
			
			$sql = "SELECT ".$col." as bracket,`exemption`,`status` FROM hr_withholdingtax_sm where ".$col." <= ?
					order by ".$col." desc
					Limit 1";
					
			$get_table_entries = $this->db->query($sql,array($total_gross_salary));
			
			if($get_table_entries->num_rows() >= 1)
			{
				$wht = $get_table_entries->row();
				if($wht){
					$withholding_tax = (($total_gross_salary - $wht->bracket) * $wht->status / 100) + $wht->exemption;
				}else{
					$withholding_tax = 0;
				}
				
				
				// print_r($wht);
				// echo '<br>';
				// echo $total_gross_salary;
				// echo '<br>';
				// echo $withholding_tax;
				// echo '<br>';
				// echo $this->db->last_query();
				// die();
				return $withholding_tax;
			}else{
				return FALSE;
			}
		}else{
			return FALSE;
		}
		
	}
	
	public function get_schedule($date)
	{
		$format = date('Y-m-d ',strtotime($date));
		
		$sql = "SELECT a.*,e.firstname,e.lastname,e.middlename
				FROM ".parent::$att_table." a
				LEFT JOIN ".parent::$emp_table." e ON e.id = a.emp_id
				WHERE a.date = ? 
				ORDER BY e.lastname ASC
				";
				
		$query = $this->db->query($sql,array($format));
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function get_dtr_by_interval($from,$to)
	{
		$from = date('Y-m-d',strtotime($from));
		$to = date('Y-m-d',strtotime($to));
		
		$sql = "SELECT e.firstname,
					   e.lastname,
					   e.middlename,
					   a.am_in,
					   a.am_out,
					   a.pm_in,
					   a.pm_out,
					   a.absent,
					   a.ot_holiday,
					   a.ot_restday,
					   a.leave,
					   a.date
				FROM ".parent::$emp_table." e
				LEFT JOIN ".parent::$att_table." a ON a.emp_id = e.id
				WHERE a.date BETWEEN ? AND ?
				ORDER BY a.date
				";
				
		$query = $this->db->query($sql,array($from,$to));
		
		// echo $this->db->last_query();
		// die();
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}

	public function get_employee_by_dept($id)
	{
		$employees = parent::$emp_table;
		$positions = parent::$pos_table;
		$departments = parent::$dep_table;
		$shift_table = parent::$shift_table;
		$shifts = parent::$shift;
		
		$sql = "SELECT e.id as emp_id,
					   e.firstname,
					   e.lastname,
					   e.middlename,
					   e.gender,
					   e.address,
					   e.status,
					   e.dependents,
					   p.position_name as position,
					   e.monthly_salary,
					   e.created_at,
					   d.department_name as dept_name,
					   concat(ss.shift_in,' - ',ss.shift_out) as shift,
					   ss.id as shift_id
				FROM {$employees} e
				LEFT JOIN {$departments} d ON d.id = e.department
				LEFT JOIN {$positions} p ON p.id = e.position
				LEFT JOIN {$shift_table} s ON s.emp_id = e.id
				LEFT JOIN {$shifts} ss ON ss.id = s.shift_id
				WHERE e.department = ? 
				OR s.active = 1
				AND s.dep_id = ?
				ORDER BY e.lastname ASC ";

		$query = $this->db->query($sql,array($id,$id));
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	
	/*+========================================================================================+
	  | Miscellaneous functions
	  |
	  +========================================================================================+
	*/
	private function get_min($time)
	{
		if(strpos($time,'.'))
		{
			$f_time =  substr($time,strpos($time,'.')+1);
			if($f_time !== 0 AND strlen($f_time) < 2)
			{
				return $f_time.'0';
			}else{
				return $f_time;
			}
		}else{
			return 1;
		}
	}
	
	private function gen_min($from,$to)
	{
		$from = $this->time_to_dec($from);
		$to = $this->time_to_dec($to);
		
		$from_minutes = $this->get_min($from);
		$to_minutes = $this->get_min($to);
		
		$interval = intval($to) - intval($from);
		return ($interval * 60) + $from_minutes + $to_minutes;
	}
	
	private function time_to_dec($time)
	{
		if(strpos($time,':',3))
		{
			return round(floatval(str_replace(':','.',substr($time,0,-3))),2);	
		}else{
			return round(floatval(str_replace(':','.',$time)),2);
		}
	}
	//return minutes to time
	private function _min2hr($job_duration)
	{
		$hours = (int)($job_duration / 60);
		$minutes = $job_duration - ($hours * 60);
		
		if($minutes !== 0 AND strlen($minutes) < 2)
		{
			$minutes = '0'.$minutes;
		}
		
		if($hours !== 0 AND strlen($hours) < 2)
		{
			$hours = '0'.$hours;
		}
		$time = array('hours' => $hours, 'minutes' => $minutes);
		return (object)$time;
	}
	
	private function _time_to_minutes($time,$to,$inspector = FALSE)
	{	
		if(is_array($time))
		{
			foreach($time as $k => $v)
			{
				if($inspector == FALSE)
				{
					if($this->time_to_dec($v->pm_out) < $this->time_to_dec($to))
					{
						$time_dec[] = $this->gen_min($v->pm_out,$to);
					}
				}else{
					if($this->time_to_dec($v->$inspector) < $this->time_to_dec($to))
					{
						$time_dec[] = $this->gen_min($v->$inspector,$to);
					}
				}
			}
		}else
		{
			if($this->time_to_dec($time) < $this->time_to_dec($to))
			{
				$time_dec[] = $this->gen_min($time,$to);
			}
		}
		return array_sum($time_dec);
	}
	
	public function delete_employee_data($id)
	{
		$this->db->where('id',$id)->delete(parent::$emp_table);
		$this->db->where('emp_id',$id)->delete(parent::$att_table);
		return TRUE;
	}
	
	public function update_role_dept($data)
	{
	
	
	
	}
}